/**
 * 
 */
package com.dsbridge.bank.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.dsbridge.bank.model.Customer;
import com.dsbridge.bank.model.DBUtils;

/**
 * @author Naveen
 * 
 */
@Repository
public class CustomerDAO {

	/**
	 * @author Naveen
	 * 
	 */

	@Autowired
	private DataSource dataSource;;
	private JdbcTemplate insert;

	public int insert(final Customer c) throws Exception {

		final String sql = "INSERT INTO CUSTOMER(CUST_NAME,TAX_ID) VALUES(?,?)";
		this.insert = new JdbcTemplate(dataSource);
		KeyHolder keyHolder = new GeneratedKeyHolder();

		insert.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(sql,
						new String[] { "CUST_ID" });
				ps.setString(1, c.getCustomername());
				ps.setInt(2, c.getTaxid());
				return ps;
			}
		}, keyHolder);

		return keyHolder.getKey().intValue();
	}

	public int update(Customer c) throws SQLException {

		this.insert = new JdbcTemplate(dataSource);
		String sql = "UPDATE CUSTOMER SET TAX_ID = ? WHERE CUST_ID =? ";

		return insert.update(sql,
				new Object[] { c.getTaxid(), c.getCustomerid() });

	}

	public Customer delete(Customer c) throws SQLException {

		Customer c1;
		this.insert = new JdbcTemplate(dataSource);
		RowMapper<Customer> mapper = new RowMapper<Customer>() {

			public Customer mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				Customer actor = new Customer();
				actor.setCustomerid(rs.getInt("cust_id"));
				actor.setCustomername(rs.getString("cust_name"));
				actor.setTaxid(rs.getInt("tax_id"));
				return actor;
			}
		};

		// notice the cast, the wrapping up of the 'id' argument
		// in an array, and the boxing of the 'id' argument as a reference type
		c1 = (Customer) insert.queryForObject(
				"select * from Customer where CUST_ID = ?", mapper,
				new Object[] { c.getCustomerid() });
		insert.update("DELETE FROM CUSTOMER WHERE CUST_ID = ?",
				new Object[] { c.getCustomerid() });

		return c1;
	}

	public Customer retrieveCustomerbyTaxid(int taxid) throws SQLException {

		Customer c = new Customer();
		String sql = "SELECT * FROM CUSTOMER WHERE TAX_ID = ?";
		this.insert = new JdbcTemplate(dataSource);
		RowMapper<Customer> mapper = new RowMapper<Customer>() {

			public Customer mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				Customer actor = new Customer();
				actor.setCustomerid(rs.getInt("cust_id"));
				actor.setCustomername(rs.getString("cust_name"));
				actor.setTaxid(rs.getInt("tax_id"));
				return actor;
			}
		};

		// notice the cast, the wrapping up of the 'id' argument
		// in an array, and the boxing of the 'id' argument as a reference type
		c = (Customer) insert.queryForObject(sql, mapper,
				new Object[] { taxid });

		return c;
	}

	@SuppressWarnings("unchecked")
	public List<Customer> showAllCustomer() {

		String sql = "SELECT * FROM CUSTOMER";
		this.insert = new JdbcTemplate(dataSource);
		RowMapper<Customer> mapper = new RowMapper<Customer>() {

			public Customer mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				Customer actor = new Customer();
				actor.setCustomerid(rs.getInt("cust_id"));
				actor.setCustomername(rs.getString("cust_name"));
				actor.setTaxid(rs.getInt("tax_id"));
				return actor;
			}
		};

		
		return (List<Customer>) insert.query(sql, mapper);

	}

}
